import requests
from lxml import etree
import pymysql
db = pymysql.connect("localhost", "root", "li197910", "3KPlan")

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 输入网页进行爬取
url = input("please input a key for search: ")
headers = {'User-Agent': 'Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) AppleWebKit/534.20 (KHTML, like Gecko) Chrome/11.0.672.2 Safari/534.20' }
response = requests.get("https://www.zhihu.com/search?type=content&q="+url, headers=headers)
html = etree.HTML(response.text)

# 用户名
username = html.xpath("/html/body/div[3]/div[1]/div/ul/li[3]/div[2]/div/div[2]/div[1]/div/span[1]/a//text()")
username = ''.join(username)

# 标题
title = html.xpath("/html/body/div[3]/div[1]/div/ul/li[2]/div[1]/a//text()")
title = ''.join(title)

# 内容
content = html.xpath("/html/body/div[3]/div[1]/div/ul//text()")
content = ''.join(content)

# 时间
contenttime = html.xpath("/html/body/div[3]/div[1]/div/ul/li[3]/div[2]/div/div[2]/div[2]/div[2]/a//text()")
contenttime = ''.join(contenttime)
print('用户名：'+username)
print('标题：'+title)
print('内容：'+content)
print('时间：'+contenttime)

# 使用 execute()  方法执行 SQL 查询
sql = "INSERT INTO zhihu VALUES (%s, %s, %s, %s, %s)"
cursor.execute(sql, ('2', username, title, content, contenttime))
db.commit()
# 关闭数据库连接
db.close()
